﻿// ====================================================================================================================
// Project: Camstar InSiteExtractor
// Source File: InSiteExtractor.cs
// Author: Roop Sin LE
// Created(MM/DD/YYYY HH:MM): 08/13/2010 9:14:21 PM
// Copyright: Camstar Inc.
// ====================================================================================================================

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Diagnostics;
using System.Linq;
using System.Runtime.InteropServices;
using InSiteExtractor.Configuration;
using InSiteExtractor.Definition;
using log4net;
using Microsoft.Office.Interop.Excel;

namespace InSiteExtractor.Data
{
    /// <summary>
    /// The class is knowned as a wrapper of WorkBook.
    /// </summary>
    internal class ExcelWorkbookUpdator
    {
        // Define a static logger variable so that it references the
        private static readonly ILog Log = LogManager.GetLogger(typeof(ExcelWorkbookUpdator));

        private readonly Dictionary<string, int> _columnMap;
        private readonly Dictionary<string, string> _colmTypeMap;
        private readonly Dictionary<string, int> _columnComments;
        private readonly string _fileName;
        private Application _excelApp;
        private string _sheetName;
        private Workbook _workBook;
        private Worksheet _workSheet;

        public ExcelWorkbookUpdator(string fileName)
        {
            _fileName = fileName;
            _columnMap = new Dictionary<string, int>();
            _colmTypeMap = new Dictionary<string, string>();
            _columnComments = new Dictionary<string, int>();
        }

        public void Open()
        {
            Log.InfoFormat("Open the excel [{0}] ......", _fileName);

            //Start Excel and get Application object.
            _excelApp = new Application { Visible = false };

            _workBook = _excelApp.Workbooks.Open(_fileName, Type.Missing, false, Type.Missing, Type.Missing,
                                                 Type.Missing, Type.Missing, Type.Missing,
                                                 Type.Missing, true, Type.Missing, Type.Missing, Type.Missing,
                                                 Type.Missing, Type.Missing);
        }

        /// <summary>
        /// Set the sheet
        /// </summary>
        /// <param name="sheetName">Sheet name</param>
        public void SetSheet(string sheetName)
        {
            _sheetName = sheetName;
            _workSheet = _workBook.Sheets[_sheetName] as Worksheet;

            // Setup Column Map
            SetupColumnMap();
            Log.Debug(_columnMap);
        }


        /// <summary>
        /// Get all sheets belongs in the workbook
        /// </summary>
        /// <returns>Sheet List</returns>
        public string[] GetSheetNames()
        {
            var sheetNames = new string[_workBook.Sheets.Count];
            int i = 0;
            foreach (object sheetBook in _workBook.Sheets)
            {
                sheetNames[i] = ((Worksheet)sheetBook).Name;
                i++;
            }

            return sheetNames;
        }

        /// <summary>
        /// Generate SQL Template
        /// </summary>
        /// <returns></returns>
        public string GenerateSqlTemplate()
        {
            // return _columnMap.Keys.Aggregate("", (current, key) => current + string.Format("{0}, ", key));

            string header = "";

            // Prepare SQL Template
            // SELECT *
            // FROM SheetName A

            string cdoName = _workSheet.Name.Replace(" ", ""); // Standard table
            string rowFilter = ConfigurationHelper.GetRowFileter(cdoName);
            cdoName = ConfigurationHelper.ConvertTableName(cdoName);
            string tableName = SqlDataExtractor.GetTableName(cdoName);

            // Build Header
            header += string.Format("-- {0} : ", cdoName);
            foreach (KeyValuePair<string, int> map in _columnMap)
            {
                header += string.Format("{0}, ", map.Key);
            }
            
            // Make it to Upper case for building SQL
            cdoName = cdoName.ToUpper();
            // Build SQL
            List<string> columns = SqlDataExtractor.ExtractColumns(tableName);

            string fromStatement = string.Format("FROM {0} ", tableName);
            string whereStatement = "";

            string sqlStatement = "SELECT \n";
            foreach (KeyValuePair<string, int> item in _columnMap)
            {
                Log.DebugFormat("Process the pair [{0}:{1}]", item.Key, item.Value);
                string columName = string.Format("{0}.{1}", tableName, item.Key);

                if (item.Key == ExtractorConst.ObjectName)
                {
                    columName = string.Format("{0}.{1}{2}", tableName, cdoName, ExtractorConst.ObjectName);
                    Log.DebugFormat("Build the key {0}", columName);
                }
                else if (item.Key == ExtractorConst.ObjectRevision) // Standard product
                {
                    Log.DebugFormat("Build the key for RDO object - Key = {0}", item.Key);
                    // This case is Revision Object
                    string baseTableName = string.Format("{0}{1}", tableName, ExtractorConst.ObjectBase);
                    string baseStandTableName = string.Format("{0}{1}", cdoName, ExtractorConst.ObjectBase);

                    fromStatement += string.Format(", {0} ", baseTableName);
                     // PRODUCTBASEID
                    whereStatement += string.Format("{0} {1}.{2}ID = {3}.{4}ID (+) \n",
                                                    (whereStatement == "" ? "WHERE" : "    AND"), tableName, baseStandTableName,
                                                    baseTableName, baseStandTableName);

                    // Correct column "NAME" --> Name is belong to Base table.
                    sqlStatement = sqlStatement.Replace(string.Format("{0}.{1}{2}", tableName, cdoName, ExtractorConst.ObjectName),
                                         string.Format("{0}.{1}{2}", baseTableName, cdoName, ExtractorConst.ObjectName));

                    columName = string.Format("{0}.{1}{2}", tableName, cdoName, ExtractorConst.ObjectRevision);
                }
                else if (_colmTypeMap.ContainsKey(item.Key) && _colmTypeMap[item.Key].Contains(ExtractorConst.Ndo)) // Mapbe it's a NDO field
                {
                    Log.DebugFormat("Processing NDO Object Type = {0}", _colmTypeMap[item.Key]);
                    // NDO Case
                    // Suggest Ndo table.
                    // How to know standard table & customized table
                    // Verify with Database.
                    Dictionary<string, string> dbInfo = SqlDataExtractor.GetDbFieldInfo(cdoName, item.Key);
                    Log.Debug(dbInfo);
                    string custTableName = item.Key;
                    string refKey = custTableName;
                    if (dbInfo.ContainsKey(ExtractorConst.CdoName))
                    {
                        custTableName = dbInfo[ExtractorConst.FieldDbTableName];
                        refKey = dbInfo[ExtractorConst.FieldDefName];
                    }
                    fromStatement += string.Format(", {0} ", custTableName);
                    whereStatement += string.Format("{0} {1}.{2}ID = {3}.{4}ID (+) \n",
                                                (whereStatement == "" ? "WHERE" : "    AND"), tableName, item.Key,
                                                custTableName, refKey);

                    columName = string.Format("{0}.{1}NAME", custTableName, item.Key);
                }
                else if (!columns.Contains(item.Key))
                {
                    Log.Debug("Processing unmatching case.");
                    columName = "NULL";
                }

                //
                string rowStatement = string.Format("    {0}{1}\t\t{2}\t\t --{3} \n", (item.Key != ExtractorConst.ObjectName? ", " : "  "), columName, item.Key,
                                                    GetColumnComments(cdoName, item.Key, item.Value)); // For print only
                sqlStatement += rowStatement;
            }

            sqlStatement += fromStatement;
            // Apply filter
            if (rowFilter != null && rowFilter != "")
            {
                Log.DebugFormat("Apply the filter {0}", rowFilter);
                whereStatement += string.Format("{0} {1} \n",
                                                    (whereStatement == "" ? "WHERE" : "    AND"), rowFilter);
            }
            sqlStatement += string.Format("\n{0}", whereStatement);

            header += string.Format("\n\n{0}", sqlStatement);

            return header;
        }

        private string GetColumnComments(string cdoName, string columName, int columnIndex)
        {
            return GetExcelColumnName(columnIndex, string.Empty);
        }

        /// <summary>
        /// Initialize
        /// </summary>
        public void Initialize()
        {
            Log.Info("Initialize ......");

            Log.Info("Initialize has completed");
        }

        /// <summary>
        /// Update a value at a cell
        /// </summary>
        /// <param name="rowIndex">Indicate which row to update</param>
        /// <param name="colName">Incidate which Column Name defined in SEMI Excel Loader</param>
        /// <param name="value">Incidate the value to be updated</param>
        public void UpdateCell(int rowIndex, String colName, string value)
        {
            Log.DebugFormat("UpdateCell [Row = {0}] - [Column Name = {1}] - [Value = {2} ......", rowIndex, colName,
                            value);
            try
            {
                colName = colName.ToUpper();
                if (_columnMap.ContainsKey(colName))
                {
                    int c = _columnMap[colName.ToUpper()];
                    UpdateCell(rowIndex, c, value);
                }
                else
                {
                    Log.WarnFormat("The column [{0}] is not defined in Excel yet!", colName);
                }
            }
            catch (Exception ex)
            {
                Log.Error(ex);
            }
        }

        public void UpdateCell(int rowIndex, int colIndex, string value)
        {
            int r = ExtractorConst.DataRowOffset + rowIndex;

            Log.DebugFormat("Updating r = {0}, c = {1}, value = {2}", r, colIndex, value);
            // ((Range)_workSheet.Cells[r, colIndex]).Value2 = value;
            ((Range)_workSheet.Cells[r, colIndex]).set_Value(Type.Missing, value);
        }

        /// <summary>
        /// Analyze Column name defined in SEMI Excel Loader & Build a map Column Name to Index
        /// </summary>
        private void SetupColumnMap()
        {
            Log.Info("SetupColumnMap ......");

            // Clean the map
            _columnMap.Clear();
            _colmTypeMap.Clear();

            
            int colInidex = 3; // Start

            while (((Range)_workSheet.Cells[ExtractorConst.ColumnNameOffset, colInidex]).Value2 != null)
            {
                string key = ((Range)_workSheet.Cells[ExtractorConst.ColumnNameOffset, colInidex]).Value2.ToString();
                // Make up the key.
                // Remove Space
                key = key.Replace(" ", "");

                // SessionValues.Factory ==> Factory
                string[] group = key.Split('.');
                if (group.Length >= 2)
                {
                    key = group[group.Length - 1];
                }

                // Remove some speical characters
                key = key.Replace('-', '_');

                // Make it in UPPER case
                key = key.ToUpper();

                if (!_columnMap.ContainsKey(key))
                {
                    _columnMap.Add(key, colInidex);
                }
                else
                {
                    key = GetExcelColumnName(colInidex, key);
                    Log.WarnFormat(
                        "{0} [Sheet = {1}] - the key [{2}] at Column Index [{3}] is existing. The key is used Excel column of {4}",
                        _workBook.Name, _workSheet.Name, key, colInidex, key);

                    _columnMap.Add(key, colInidex);
                }



                // Support for field Type
                if (((Range)_workSheet.Cells[ExtractorConst.ColumnTypeOffset, colInidex]).Value2 != null)
                {
                    string type =
                        ((Range) _workSheet.Cells[ExtractorConst.ColumnTypeOffset, colInidex]).Value2.ToString().ToUpper();
                    Log.DebugFormat("Sheet = {0} - Key = {1} - Type = {2}", _sheetName, key, type);
                    // support NDO);
                    if (type.Contains(ExtractorConst.Ndo) && !_colmTypeMap.ContainsKey(type))
                    {
                        Log.DebugFormat("Process the filed as NDO object - Sheet = {0} - Key = {1} - Type = {2}", _sheetName, key, type);
                        _colmTypeMap.Add(key, type);
                    }
                    else if (type.StartsWith(ExtractorConst.SubEntity)) // SubEntity (Not supported)
                    {
                        // Detect SubEntity Object
                        if (type.Split(' ').Length > 2)
                        {
                            _colmTypeMap.Add(key, string.Format("{0}{1}", ExtractorConst.SubEntity, type[1]));
                        }
                    }
                    else
                    {
                        Log.InfoFormat("Not support type - Sheet = {0} - Key = {1} - Type = {2}", _sheetName, key, type);
                    }
                }
                colInidex++;
            }
        }

        /// <summary>
        /// This method is used to convert number (1, 2, 3...) to Letter (A, B, ..AA, ..) 
        /// </summary>
        /// <param name="columnNumber">Indicate the number required to convert</param>
        /// <param name="subPrefix">Indicate the subPrefix to add</param>
        /// <returns>A letter corresponding to input number</returns>
        private static string GetExcelColumnName(int columnNumber, string subPrefix)
        {
            int dividend = columnNumber;
            string columnName = String.Empty;

            while (dividend > 0)
            {
                int modulo = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + modulo) + columnName;
                dividend = ((dividend - modulo) / 26);
            }

            return string.Format("{0}{1}{2}", columnName, (subPrefix!=string.Empty || subPrefix!=""?"_":""), subPrefix);
        }

        /// <summary>
        /// Close the workbook and free memory
        /// </summary>
        public void Close(bool save)
        {
            Log.Info("Close ......");
            _excelApp.Visible = false;
            if (save) 
                _workBook.Save();
            _workBook.Close(false, Type.Missing, Type.Missing);

            Marshal.ReleaseComObject(_workBook);

            // Cleanup
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }
    }
}